/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.mysql;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author admin
 */
import com.objects.user;
import com.objects.feed;
import java.util.ArrayList;

public class mysqlFeeds {

    public static Boolean checkIfFeedExists (String URL) throws Exception
    {
        mysqlConnector con = new mysqlConnector();
        try
        {

            ArrayList<feed> retArr=new ArrayList();
            Statement state = con.verbMySQL.createStatement();
            ResultSet result = state.executeQuery("SELECT * FROM TBLFeeds WHERE FeedURL='"+URL+"'");
            if (result.next())
            {
                return true;

            }
            else
            {
                return false;
            }
        }
        catch (SQLException ex)
        {
             Logger.getLogger(mysqlFeeds.class.getName()).log(Level.SEVERE,null,ex);

        }
        finally
        {
            con.verbMySQL.close();
        }
        return false;
    }
    
     public static Boolean checkIfFeedIsSubscribed (int userid, int feedid) throws Exception
    {
        mysqlConnector con = new mysqlConnector();
        try
        {

            ArrayList<feed> retArr=new ArrayList();
            Statement state = con.verbMySQL.createStatement();
            ResultSet result = state.executeQuery("SELECT * FROM TBLSubscription WHERE FeedID='"+feedid+"' AND UserID='"+userid+"'");
            if (result.next())
            {
                return true;

            }
            else
            {
                return false;
            }
        }
        catch (SQLException ex)
        {
             Logger.getLogger(mysqlFeeds.class.getName()).log(Level.SEVERE,null,ex);

        }
        finally
        {
            con.verbMySQL.close();
        }
        return false;
    }



    public static ArrayList<feed> getAllFeeds() throws Exception
    {
        mysqlConnector con = new mysqlConnector();
        try
        {

            ArrayList<feed> retArr=new ArrayList();
            Statement state = con.verbMySQL.createStatement();
            ResultSet result = state.executeQuery("SELECT * FROM TBLFeeds");
            while (result.next())
            {
                retArr.add(new feed(result.getInt(1),result.getString(3),result.getString(2),result.getInt(4)));

            }
            return retArr;

        }
        catch (SQLException ex)
        {
             Logger.getLogger(mysqlFeeds.class.getName()).log(Level.SEVERE,null,ex);

        }
        finally
        {
            con.verbMySQL.close();
        }
        return null;
    }

    public static ArrayList<feed> getFeedsFromUser (int userid) throws Exception
    {
        mysqlConnector con = new mysqlConnector();
        try
        {

            ArrayList<feed> retArr=new ArrayList();
            Statement state = con.verbMySQL.createStatement();
            ResultSet result = state.executeQuery("SELECT * FROM TBLFeeds,TBLSubscription WHERE TBLSubscription.UserID='"+userid+"' AND TBLFeeds.FeedID=TBLSubscription.FeedID ORDER BY TBLFeeds.CategoryID ASC");
            while (result.next())
            {
                retArr.add(new feed(result.getInt(1),result.getString(3),result.getString(8),result.getInt(4)));

            }
            return retArr;

        }
        catch (SQLException ex)
        {
             Logger.getLogger(mysqlFeeds.class.getName()).log(Level.SEVERE,null,ex);

        }
        finally
        {
            con.verbMySQL.close();
        }
        return null;
    }

    public static feed getFeedByName (String fName) throws Exception
    {
        mysqlConnector con = new mysqlConnector();
        try
        {

            feed retFeed=new feed();
            Statement state = con.verbMySQL.createStatement();
            ResultSet result = state.executeQuery("SELECT * FROM TBLFeeds WHERE FeedName='"+fName+"'");
            if (result.next())
            {
                retFeed=new feed(result.getInt(1),result.getString(3),result.getString(2),result.getInt(4));

            }
            return retFeed;

        }
        catch (SQLException ex)
        {
             Logger.getLogger(mysqlFeeds.class.getName()).log(Level.SEVERE,null,ex);

        }
        finally
        {
            con.verbMySQL.close();
        }
        return null;
    }

     public static Boolean checkIfFeednameExists (String fName) throws Exception
    {
        mysqlConnector con = new mysqlConnector();
        try
        {

            feed retFeed=new feed();
            Statement state = con.verbMySQL.createStatement();
            ResultSet result = state.executeQuery("SELECT * FROM TBLFeeds WHERE FeedName='"+fName+"'");
            if (result.next())
            {
               return true;

            }
            else
            {
                return false;
            }


        }
        catch (SQLException ex)
        {
             Logger.getLogger(mysqlFeeds.class.getName()).log(Level.SEVERE,null,ex);

        }
        finally
        {
            con.verbMySQL.close();
        }
        return false;
    }

    public static ArrayList<feed> getFeedsFromUserOfCategory (int userid,int catID) throws Exception
    {
        mysqlConnector con = new mysqlConnector();
        try
        {

            ArrayList<feed> retArr=new ArrayList();
            Statement state = con.verbMySQL.createStatement();
            ResultSet result = state.executeQuery("SELECT * FROM TBLFeeds,TBLSubscription WHERE TBLSubscription.UserID='"+userid+"' AND TBLFeeds.FeedID=TBLSubscription.FeedID AND TBLFeeds.CategoryID='"+catID+"' ORDER BY TBLFeeds.CategoryID ASC");
            while (result.next())
            {
                retArr.add(new feed(result.getInt(1),result.getString(3),result.getString(8),result.getInt(4)));
                
            }
            return retArr;

        }
        catch (SQLException ex)
        {
             Logger.getLogger(mysqlFeeds.class.getName()).log(Level.SEVERE,null,ex);

        }
        finally
        {
            con.verbMySQL.close();
        }
        return null;
    }

   public static Boolean saveNewFeed (String feedurl, String feedname, int catid) throws Exception
   {
        mysqlConnector con = new mysqlConnector();
        try
        {

           
            Statement state = con.verbMySQL.createStatement();
            if (state.executeUpdate("INSERT INTO TBLFeeds (FeedURL,FeedName,CategoryID) VALUES ('"+feedurl+"','"+feedname+"','"+catid+"')")>0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch (SQLException ex)
        {
             Logger.getLogger(mysqlFeeds.class.getName()).log(Level.SEVERE,null,ex);

        }
        finally
        {
            con.verbMySQL.close();
        }
        return false;
   }

   public static Boolean subscribeFeed (int userid,int feedid,Boolean unsus,String indName) throws Exception
   {
        mysqlConnector con = new mysqlConnector();
        try
        {
            String sql="";
            if (unsus)
            {
                sql="DELETE FROM TBLSubscription WHERE UserID='"+userid+"' AND FeedID='"+feedid+"'";
            }
            else
            {
                sql="INSERT INTO TBLSubscription (UserID,FeedID,IndividualName) VALUES ('"+userid+"','"+feedid+"','"+indName+"')";
            }

            Statement state = con.verbMySQL.createStatement();
            if (state.executeUpdate(sql)>0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch (SQLException ex)
        {
             Logger.getLogger(mysqlFeeds.class.getName()).log(Level.SEVERE,null,ex);

        }
        finally
        {
            con.verbMySQL.close();
        }
        return false;
   }


   
}
